package com.teach.util;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.annotation.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.StringJoiner;

public class DBUtil {

  //获取数据库连接对象
  private static String url;
  private static String user;
  private static String password;

  static {
    //加载数据库驱动
    try {
      Properties config = new Properties();
      config.load(DBUtil.class.getResourceAsStream("jdbc.properties"));
      url = config.getProperty("jdbc.url");
      user = config.getProperty("jdbc.user");
      password = config.getProperty("jdbc.password");
      Class.forName(config.getProperty("jdbc.driver", "com.mysql.jdbc.Driver"));
    } catch (Exception ex) {
      ex.printStackTrace();
      System.exit(0);
    }
  }

  private DBUtil() {
  }

  /**
   * 获取数据库连接
   * @return
   * @throws SQLException
   */
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, user, password);
  }


  /**
   * 设置预处理的语句对象的参数
   * @param pstmt - 预处理的语句对象
   * @param objs - 变长参数
   * @throws SQLException
   */
  public static void setParam(PreparedStatement pstmt,Object... objs) throws SQLException{
    if(objs==null||objs.length==0){
      return;
    }
    for(int i=0;i<objs.length;i++){
      pstmt.setObject(i+1, objs[i]);
    }
  }

  /**
   * 封装jdbc的sql语句的执行(insert,update,delete)
   * @param sql
   * @param objs
   * @return
   * @throws SQLException
   */
  public static int execute(String sql,Object... objs)throws SQLException {
    try(
        Connection conn = getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
    ){
      setParam(pstmt, objs);
      return pstmt.executeUpdate();
    }
  }



  public static void close(Connection conn, Statement stmt) {
    try {
      stmt.close();
    } catch (Exception e) {
    }
    try {
      conn.close();
    } catch (Exception e) {
    }
  }

  public static void close(Connection conn, Statement stmt, ResultSet rs) {
    try {
      rs.close();
    } catch (Exception e) {
    }
    close(conn, stmt);
  }

  public static <E> E selectOne(String sql,Mapper<E> mapper,Object... args)throws SQLException{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try{
      conn = getConnection();
      pstmt = conn.prepareStatement(sql);
      setParam(pstmt, args);
      rs = pstmt.executeQuery();
      if(rs.next()){
        return mapper.map(rs);
      }else{
        throw new SQLException("没有找到对应的记录");
      }
    }finally {
      close(conn,pstmt,rs);
    }
  }

  public static <E> E selectOne(String sql,Class<E> cls,Object... args)throws SQLException{
    return selectOne(sql, new EntityLoader<>(cls), args);
  }

  public static <E> List<E> selectList(String sql, Mapper<E> mapper, Object... args)throws SQLException{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try{
      conn = DBUtil.getConnection();
      pstmt = conn.prepareStatement(sql);
      setParam(pstmt,args);
      rs = pstmt.executeQuery();
      List<E> list = new ArrayList<>();
      while (rs.next()) {
        list.add(mapper.map(rs));
      }
      return list;
    }finally {
      DBUtil.close(conn,pstmt,rs);
    }
  }
  public static <E> List<E> selectList(String sql, Class<E> cls, Object... args)throws SQLException{
    return selectList(sql,new EntityLoader<>(cls),args);
  }

  public static BigDecimal selectBigDecimal(String sql,Object... args)throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try{
      conn = DBUtil.getConnection();
      pstmt = conn.prepareStatement(sql);
      setParam(pstmt,args);
      rs = pstmt.executeQuery();
      if(rs.next()){
        return rs.getBigDecimal(1);
      }else{
        throw new SQLException("没有找到对应的记录");
      }
    }finally {
      DBUtil.close(conn,pstmt,rs);
    }
  }

  public static Object selectObjct(String sql,Object... args)throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try{
      conn = DBUtil.getConnection();
      pstmt = conn.prepareStatement(sql);
      setParam(pstmt,args);
      rs = pstmt.executeQuery();
      if(rs.next()){
        return rs.getObject(1);
      }else{
        throw new SQLException("没有找到对应的记录");
      }
    }finally {
      DBUtil.close(conn,pstmt,rs);
    }
  }

  public static String selectString(String sql,Object... args)throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try{
      conn = DBUtil.getConnection();
      pstmt = conn.prepareStatement(sql);
      setParam(pstmt,args);
      rs = pstmt.executeQuery();
      if(rs.next()){
        return rs.getString(1);
      }else{
        throw new SQLException("没有找到对应的记录");
      }
    }finally {
      DBUtil.close(conn,pstmt,rs);
    }
  }


  public static int insert(Object entity) throws SQLException {
    return insert(entity, true);
  }

  /**
   * 有选择性的添加,添加的非空属性的字段
   * @param entity
   * @return
   * @throws SQLException
   */
  public static int insertSelevtion(Object entity) throws SQLException{
    return insert(entity, false);
  }

  /**
   *
   * @param entity
   * @param nullable 是否允许插入null
   * @return
   * @throws SQLException
   */
  private static int insert(Object entity,boolean nullable)throws SQLException {
    if(entity==null){
      throw new SQLException("不能插入一个空对象");
    }
    StringBuilder sql = new StringBuilder();
    List<Object> params = new ArrayList<>();

    Class<?> cls = entity.getClass();
    //生成sql
    sql.append("insert into ");
    sql.append(getTableName(cls));

    PropertyDescriptor[] pds = getPropertyDescriptors(cls);//得到所有的属性
    StringJoiner cols = new StringJoiner(",");
    StringJoiner values = new StringJoiner(",");
    for(PropertyDescriptor pd : pds){
      if(exclude(pd)){
        continue;
      }

      Object param = null;//参数值
      try {
        param = pd.getReadMethod().invoke(entity);//param =entity.getXxx();
      } catch (Exception e) {
        throw new SQLException("获取参数值失败:"+pd.getReadMethod().toString(),e);
      }
      if(param==null&&!nullable){
        continue;
      }

      params.add(param);

      cols.add(getColumnName(pd));
      values.add("?");
    }
    sql.append('(');
    sql.append(cols);
    sql.append(") values(");
    sql.append(values);
    sql.append(")");

//    System.out.println(sql);
//    System.out.println(params);

    return execute(sql.toString(), params.toArray());
//    return 0;
  }

  public static int updateById(Object entity,boolean nullable)throws SQLException {
    if(entity==null){
      throw new SQLException("不能插入一个空对象");
    }
    StringBuilder sql = new StringBuilder();
    List<Object> params = new ArrayList<>();

    Class<?> cls = entity.getClass();

    sql.append("update ");
    sql.append(getTableName(cls));
    sql.append(" set ");

    StringJoiner cols = new StringJoiner(",");
    String where = null;
    Object idValue = null;
    for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
      if(exclude(pd)){
        continue;
      }

      Object param = null;//参数值
      try {
        param = pd.getReadMethod().invoke(entity);//param =entity.getXxx();
      } catch (Exception e) {
        throw new SQLException("获取参数值失败:"+pd.getReadMethod().toString(),e);
      }
      if(isId(pd)){
        if(param==null){
          throw new SQLException("实体"+cls.getName()+"."+pd.getName()+"不能为null");
        }
        where = " where "+getColumnName(pd)+"=?";
        idValue = param;
        continue;
      }
      if(param==null&&!nullable){
        continue;
      }

      {
        params.add(param);
        cols.add(getColumnName(pd) + "=?");
      }

    }
    if(where==null){
      throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
    }

    sql.append(cols.toString());
    sql.append(where);
    params.add(idValue);

//    System.out.println(sql);
//    System.out.println(params);
    return execute(sql.toString(), params.toArray());
//    return 0;
  }


  public static int deleteById(Serializable id, Class<?> cls)throws SQLException {
    if(id==null){
      throw new SQLException("主键不能为空");
    }
    if(cls == null){
      throw new SQLException("实体类不能为空");
    }
    StringBuilder sql = new StringBuilder();

    sql.append("delete from ");
    sql.append(getTableName(cls));

    String where = null;
    for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
      if(exclude(pd)){
        continue;
      }

      if(isId(pd)){
        where = " where "+getColumnName(pd)+"=?";
        break;
      }

    }
    if(where==null){
      throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
    }

    sql.append(where);
//    System.out.println(sql);
//    System.out.println(params);
    return execute(sql.toString(), id);
//    return 0;
  }


  public static <E> E selectById(Serializable id, Class<E> cls)throws SQLException {
    if(id==null){
      throw new SQLException("主键不能为空");
    }
    if(cls == null){
      throw new SQLException("实体类不能为空");
    }
    StringBuilder sql = new StringBuilder();

    sql.append("select * from ");
    sql.append(getTableName(cls));

    String where = null;
    for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
      if(exclude(pd)){
        continue;
      }

      if(isId(pd)){
        where = " where "+getColumnName(pd)+"=?";
        break;
      }

    }
    if(where==null){
      throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
    }

    sql.append(where);
//    System.out.println(sql);
//    System.out.println(params);
    return selectOne(sql.toString(), cls, id);
  }


  public static <E> List<E> selectList(Class<E> cls,int index,int len)throws SQLException {
    if(cls == null){
      throw new SQLException("实体类不能为空");
    }
    StringBuilder sql = new StringBuilder();

    sql.append("select * from ");
    sql.append(getTableName(cls));

    String id = null;
    for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
      if(exclude(pd)){
        continue;
      }
      if(isId(pd)){
        id = getColumnName(pd);
        break;
      }
    }
    if(id!=null) {
      sql.append(" order by ");
      sql.append(id);
      sql.append(" desc");
    }
    sql.append(" limit ?,?");
//    System.out.println(sql);
//    System.out.println(params);
    return selectList(sql.toString(), cls, index,len);
  }


  public static Long count(Class<?> cls)throws SQLException {
    if(cls == null){
      throw new SQLException("实体类不能为空");
    }
    StringBuilder sql = new StringBuilder();
    sql.append("select count(*) from ");
    sql.append(getTableName(cls));
    return selectBigDecimal(sql.toString()).longValue();
  }


  /**
   * 根据属性获取字段名
   * @param pd
   * @return
   */
  public static String getColumnName(PropertyDescriptor pd){

    Method m = pd.getReadMethod();//获取getter
    if(m!=null && m.isAnnotationPresent(Name.class)){
      return m.getAnnotation(Name.class).value();
    }

    //setter上要就没有写@Id
    m = pd.getWriteMethod();//获取setter
    if(m!=null && m.isAnnotationPresent(Name.class)){
      return m.getAnnotation(Name.class).value();
    }

    //获取对应的Field有没有写@Id
//    System.out.println(pd.getReadMethod().getDeclaringClass());
    try {
      Field f = pd.getReadMethod().getDeclaringClass().getDeclaredField(pd.getName());
//      System.out.println(f);
      if(f!=null && f.isAnnotationPresent(Name.class)){
        return f.getAnnotation(Name.class).value();
      }
    }catch (Exception ex){
    }
    String columnName = pd.getName();
    columnName = columnName.replaceAll("([A-Z])", "_$1").toLowerCase();
    return columnName;
  }

  /**
   * 排除的属性
   * @param pd
   * @return
   */
  public static boolean exclude(PropertyDescriptor pd){
    return pd.getName().equals("class") || pd.getReadMethod()==null || pd.getWriteMethod()==null;
  }


  /**
   * 根据class获取表名
   * @param cls
   * @return
   */
  public static String getTableName(Class<?> cls){
    String tableName = cls.getSimpleName();
    if(cls.isAnnotationPresent(Name.class)){
      Name name = cls.getAnnotation(Name.class);
      tableName = name.value();
    }else {
      //大驼峰命名规则转换成蛇形命名规则
      tableName = tableName.replaceAll("([A-Z])", "_$1").toLowerCase().substring(1);
    }
    return tableName;
  }

  public static PropertyDescriptor[] getPropertyDescriptors(Class<?> cls)throws SQLException{
    try {
      return Introspector.getBeanInfo(cls).getPropertyDescriptors();
    } catch (IntrospectionException e) {
      throw new SQLException("获取bean的属性失败",e);
    }
  }
  /**
   * 判断该属性是否是主键
   */
  public static boolean isId(PropertyDescriptor pd){
    //getter上要就没有写@Id
    Method m = pd.getReadMethod();//获取getter
    if(m!=null && m.isAnnotationPresent(Id.class)){
//      m.isAnnotationPresent(Id.class);//是否定义了某个注解
      return true;
    }

    //setter上要就没有写@Id
    m = pd.getWriteMethod();//获取setter
    if(m!=null && m.isAnnotationPresent(Id.class)){
      return true;
    }

    //获取对应的Field有没有写@Id
//    System.out.println(pd.getReadMethod().getDeclaringClass());
    try {
      Field f = pd.getReadMethod().getDeclaringClass().getDeclaredField(pd.getName());
//      System.out.println(f);
      if(f!=null && f.isAnnotationPresent(Id.class)){
        return true;
      }
    }catch (Exception ex){

    }


    return false;
  }


  public static interface Mapper<E> {
    E map(ResultSet rs)throws SQLException;
  }

  /**
   * - 将结果集保存到实体对象中,需要遵守数据库表列名下划线命名规则到实体对象属性的小驼峰命名规则的转换
   *
   * @author J.L.Zhou
   * @param <T>
   */
  private static class EntityLoader<T> implements Mapper<T> {

    private Class<T> cls;

    public EntityLoader(Class<T> cls) {
      this.cls = cls;
    }

    @Override
    public T map(ResultSet rs) throws SQLException {
      T obj = null;
      try {
        obj = cls.newInstance();
      } catch (Exception e) {
        throw new SQLException("调用无参构造方法实例化错误：" + cls.getName(), e);
      }

      for (PropertyDescriptor pd : getPropertyDescriptors(cls)) {
        if (exclude(pd)) {
          continue;
        }
        String columnName = getColumnName(pd);

        try {
          Object value = null;
          try {
            value = rs.getObject(columnName);
          } catch (Exception ex) {
            // 警告，字段不存在
          }
          if (value != null) {
            if (pd.getPropertyType() == Integer.class) {
              value = rs.getInt(columnName);
            } else if (pd.getPropertyType() == Character.class) {
              value = rs.getString(columnName).charAt(0);
            } else if (pd.getPropertyType() == java.util.Date.class) {
              value = new java.util.Date(rs.getTimestamp(columnName).getTime());
            } else {
              try {
                value = ResultSet.class
                    .getMethod("get" + pd.getPropertyType().getSimpleName(), String.class)
                    .invoke(rs, columnName);
              } catch (Exception ex) {
              }
            }
            pd.getWriteMethod().invoke(obj, value);
          }

        } catch (Exception e) {
          throw new SQLException("设置字段错误：" + columnName, e);
        }
      }
      return obj;
    }
  }


  /**
   * DAO常见的方法
   * @author J.L.Zhou
   *
   */
  public static interface BaseDAO<E> {

    /**
     * 添加就一个实体
     * @param entity
     * @return 影响行数
     * @throws SQLException
     */
    int insert(E entity)throws SQLException;

    /**
     * 根据主键来更新其它的列的数据
     * @param entity
     * @return 影响行数
     * @throws SQLException
     */
    int updateById(E entity)throws SQLException;

    /**
     * 根据主键来删除一条记录
     * @param id
     * @return 影响行数
     * @throws SQLException
     */
    int deleteById(Serializable id)throws SQLException;

    /**
     * 根据主键查询一条记录
     * @param id
     * @return 实体对象
     * @throws SQLException
     */
    E selectById(Serializable id)throws SQLException;

    /**
     * 返回索引index开始的len条记录
     * @param index
     * @param len
     * @return
     * @throws SQLException
     */
    List<E> select(int index,int len)throws SQLException;

    /**
     * 统计所有记录数
     * @return
     * @throws SQLException
     */
    long count()throws SQLException;
  }

  public static class BaseDAOImpl<E> implements BaseDAO<E> {

    private Class<E> cls;

    public BaseDAOImpl(){
      cls = (Class<E>)((ParameterizedType)getClass().getGenericSuperclass()).getActualTypeArguments()[0];
    }

    @Override
    public int insert(E entity) throws SQLException {
      return DBUtil.insertSelevtion(entity);
    }

    @Override
    public int updateById(E entity) throws SQLException {
      return DBUtil.updateById(entity,true);
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
      return DBUtil.deleteById(id,cls);
    }

    @Override
    public E selectById(Serializable id) throws SQLException {
      return DBUtil.selectById(id,cls);
    }

    @Override
    public List<E> select(int index, int len) throws SQLException {
      return DBUtil.selectList(cls,index,len);
    }

    @Override
    public long count() throws SQLException {
      return DBUtil.count(cls);
    }
  }

  /**
   * 主键
   */
  @Target({ElementType.METHOD,ElementType.FIELD})
  @Retention(RetentionPolicy.RUNTIME)
  @Documented
  @Inherited
  public static @interface Id{

  }

  /**
   * 定义属性的类,对应的字段名和表名
   */
  @Target({ElementType.METHOD,ElementType.FIELD,ElementType.TYPE})
  @Retention(RetentionPolicy.RUNTIME)
  @Documented
  @Inherited
  public static @interface Name {
    String value();
  }
}
